package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import model.q;
import util.ConnMySQL;

public class QDao {

    public int save(q q){
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet affectedSet = null;

        String idusers = null;
        try {
            conn = ConnMySQL.getConnection();
            String sql = "insert into qa(question, questionerId,questionTime,questionContext) values(?, ?, ?, ?)";
//			[!]注意这里的insert获取id的写法
            preStmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preStmt.setString(1, q.getQuestion());
            preStmt.setString(2, q.getQuestionerId());
            preStmt.setString(3, q.getQuestionTime());
            preStmt.setString(4, q.getQuestionContext());
            preStmt.executeUpdate();
            affectedSet = preStmt.getGeneratedKeys();
            if (affectedSet.next()) {
                idusers = affectedSet.getString(1);
                System.out.println(idusers);
            }
            return 1;
        } catch  (SQLDataException ex) {
            if (ex.getErrorCode()==1062){
                return 0;
            }
            ex.printStackTrace();
            return 0;
        }catch (Exception ex){
            ex.printStackTrace();
            return 0;
        }finally {
            if (affectedSet != null) {
                try {
                    affectedSet.close();
                    affectedSet = null;
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

            if (preStmt != null) {
                try {
                    preStmt.close();
                    preStmt = null;
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

    }

    public List<q> findAll() throws Exception {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String sql = "select * from qa";
        conn=ConnMySQL.getConnection();
        if (conn != null) {
            try {
                preStmt = conn.prepareStatement(sql);
                rs = preStmt.executeQuery();
                List<q> qList = rsToQa(rs);
                return qList;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<q> findbyquestion(String question) throws Exception {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String sql = "select * from qa where question like '%"+question+"%'";
        conn=ConnMySQL.getConnection();
        if (conn != null) {
            try {
                preStmt = conn.prepareStatement(sql);
                rs = preStmt.executeQuery();
                List<q> qList = rsToQa(rs);
                return qList;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public q findById(String idQA) throws Exception {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM qa where idQA=?";
        conn=ConnMySQL.getConnection();
        if (conn != null) {
            try {
                preStmt = conn.prepareStatement(sql);
                preStmt.setString(1, idQA);
                rs = preStmt.executeQuery();
                q q = rsToQa(rs).get(0);
                return q;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    private List<q> rsToQa(ResultSet rs) {
        List<q> qList = new ArrayList<>();
        try {
            while (rs.next()) {
                String idQA = rs.getString("idQA");
                String question = rs.getString("question");
                String questionerId = rs.getString("questionerId");
                String questionTime = rs.getString("questionTime");
                String questionContext = rs.getString("questionContext");
                q q = new q(idQA,question,questionerId,questionTime,questionContext);
                qList.add(q);
            }
            return qList;
        } catch (SQLException e) {
            processSqlError(e);
        }
        return null;
    }

    private void processSqlError(Exception e) {
        e.printStackTrace();
    }

    public static void main(String[] args) {

    }

}
